# -*- coding: UTF-8 -*-
"""
@auth:buxiangjie
@date:2020-05-12 11:26:00
@describe: 
"""
import asyncio
import json

from typing import Optional

import aiomysql

from app.common.common_func import Common, Encoder
from app.db.get_sql_data import GetSqlData
from app.log.ulog import logger_
from aiomysql.pool import Pool


class ToolsSql(GetSqlData):

	@staticmethod
	async def conn_database(environment, source: Optional[str] = "saas"):
		"""
		数据库连接
		:param source: str
		:type environment:str
		"""
		yaml_data = Common.get_yaml_data("config", "database.yaml")
		config = yaml_data[environment][source]
		try:
			connect = await aiomysql.connect(**config)
			return connect
		except Exception as e:
			logger_.error(e)
			raise e

	@staticmethod
	async def conn_db_pool(environment: str, source: str) -> Pool:
		yaml_data = Common.get_yaml_data("config", "database.yaml")
		config = yaml_data[environment][source]
		try:
			pool = await aiomysql.create_pool(**config)
			logger_.info(f"建立数据库链接池:{environment}-{source}")
			return pool
		except Exception as e:
			logger_.error(e)
			raise e

	@staticmethod
	async def exec_update(sql: str, db_conn: Pool, source: Optional[str] = "saas"):
		"""执行更新语句"""
		logger_.info(f"执行sql:{sql}")
		async with db_conn.acquire() as conn:
			cur = await conn.cursor()
			await cur.execute(sql)
			await conn.commit()

	@staticmethod
	async def change_repayment_plan_date(
			environment: str,
			period: int,
			date: str,
			project_id: str,
			**kwargs
	) -> None:
		"""修改还款计划应还日期"""
		asset_id = GetSqlData.get_asset_id(environment, project_id)
		plan_table = 'repayment_plan_0' + GetSqlData.get_sub_table(environment, asset_id)
		sql = f"""
					update {plan_table}
					set plan_pay_date='{date}'
					where asset_id='{asset_id}'
						and period={period}
						and repayment_status=1;
				"""
		await ToolsSql.exec_update(sql, kwargs["saas_db"])

	@staticmethod
	async def del_asset_data(environment: str, asset_id: str, **kwargs) -> str:
		"""删除资产相关数据"""
		if asset_id is not None:
			table_index = GetSqlData.get_sub_table(environment, asset_id)
			plan_table = 'repayment_plan_0' + table_index
			fee_table = 'fee_plan_0' + table_index
			rd_table = 'repayment_detail_0' + table_index
			od_table = 'overdue_detail_0' + table_index
			user_plan_table = 'user_repayment_plan_0' + table_index
			user_fee_table = 'user_fee_plan_0' + table_index
			user_rd_table = 'user_repayment_plan_0' + table_index
			sql1 = f"""delete from {plan_table} where asset_id='{asset_id}';"""
			sql2 = f"""delete from asset where id='{asset_id}';"""
			sql3 = f"""delete from asset_extra where asset_id='{asset_id}';"""
			sql4 = f"""delete from asset_fee where asset_id='{asset_id}';"""
			sql5 = f"""delete from asset_swap_apply where asset_id='{asset_id}';"""
			sql6 = f"""delete from asset_swap_detail where asset_id='{asset_id}';"""
			sql7 = f"""delete from overdue where asset_id='{asset_id}';"""
			sql8 = f"""delete from {fee_table} where asset_id='{asset_id}';"""
			sql9 = f"""delete from {rd_table} where asset_id='{asset_id}';"""
			sql10 = f"""delete from {od_table} where asset_id='{asset_id}';"""
			sql11 = f"""delete from repayment where asset_id='{asset_id}';"""
			sql12 = f"""delete from {user_fee_table} where asset_id='{asset_id}';"""
			sql13 = f"""delete from {user_plan_table} where asset_id='{asset_id}';"""
			sql14 = f"""delete from {user_rd_table} where asset_id='{asset_id}';"""
			sql15 = f"""delete from relief_apply where asset_id = '{asset_id}';"""
			sql16 = f"""delete from relief_detail where asset_id = '{asset_id}';"""
			ds_sql15 = f"""delete from ds_asset where id='{asset_id}';"""
			ds_sql16 = f"""delete from ds_asset_swap_apply where asset_id='{asset_id}';"""
			ds_sql17 = f"""delete from ds_asset_swap_detail where asset_id='{asset_id}';"""
			ds_sql18 = f"""delete from ds_{fee_table} where asset_id='{asset_id}';"""
			ds_sql19 = f"""delete from ds_repayment where asset_id='{asset_id}';"""
			ds_sql20 = f"""delete from ds_{rd_table} where asset_id='{asset_id}';"""
			ds_sql21 = f"""delete from ds_{plan_table} where asset_id='{asset_id}';"""
			ds_sql22 = f"""delete from ds_fund_asset where id='{asset_id}';"""
			ds_sql23 = f"""delete from ds_fund_repayment where asset_id='{asset_id}';"""
			ds_sql24 = f"""delete from ds_fund_{rd_table} where asset_id='{asset_id}';"""
			ds_sql25 = f"""delete from ds_relief_apply where asset_id='{asset_id}';"""
			ds_sql26 = f"""delete from ds_relief_detail where asset_id='{asset_id}';"""

			saas_sql_list = []
			ds_sql_list = []
			for key, value in list(locals().items()):
				if key[:3] == "sql" and str.isdigit(key[3:]):
					saas_sql_list.append(value)
				elif key[:2] == "ds" and str.isdigit(key[6:]):
					ds_sql_list.append(value)
			for i in saas_sql_list:
				await ToolsSql.exec_update(i, kwargs["saas_db"])
			for i in ds_sql_list:
				await ToolsSql.exec_update(i, kwargs["data_station_db"])
			return "执行完成"

	@staticmethod
	async def del_project_data(project_id: str, **kwargs):
		"""删除进件相关数据"""
		sql1 = f"""delete from project_detail where id='{project_id}';"""
		sql2 = f"""delete from project_customer_detail where project_id='{project_id}';"""
		sql3 = f"""delete from project_enterprise_detail where project_id='{project_id}';"""
		sql4 = f"""delete from project_entity_detail where project_id='{project_id}';"""
		sql5 = f"""delete from project_extra_detail where project_id='{project_id}';"""
		sql6 = f"""delete from repayment_binding_card where project_id='{project_id}';"""
		sql_list = [sql1, sql2, sql3, sql4, sql5, sql6]
		for i in sql_list:
			await ToolsSql.exec_update(i, kwargs["saas_db"])

	@staticmethod
	async def del_credit_data(environment: str, source_user_id: str, **kwargs) -> None:
		"""删除授信信息"""
		rep = ToolsSql.select_credit(environment, source_user_id)
		if rep is not None:
			credit_id = rep["id"]
			sql1 = f"""delete from credit where credit.id='{credit_id}';"""
			sql2 = f"""delete from credit_customer where credit_customer.credit_id='{credit_id}';"""
			sql3 = f"""delete from credit_entity where credit_entity.credit_id='{credit_id}';"""
			sql4 = f"""delete from credit_extra where credit_extra.credit_id='{credit_id}';"""
			sql = [sql1, sql2, sql3, sql4]
			for s in sql:
				await ToolsSql.exec_update(s, kwargs["saas_db"])

	@staticmethod
	async def del_user_amount(environment: str, source_user_id: str, **kwargs) -> None:
		"""删除用户额度信息"""
		if amount_user := ToolsSql.select_user_amount(environment, source_user_id):
			user_id = amount_user["user_id"]
			sql1 = f"""delete from amount where user_id='{user_id}';"""
			sql2 = f"""delete from amount_detail where user_id='{user_id}';"""
			sqls = [sql1, sql2]
			for s in sqls:
				await ToolsSql.exec_update(s, kwargs["nebula_db"])
		elif project_user := ToolsSql.select_project(environment, source_user_id):
			user_id = project_user[0]["user_id"]
		else:
			user_id = None
			logger_.warning(f"未查询到用户信息:{source_user_id}")
		if user_id:
			sql = f"""delete from user_info where id='{user_id}';"""
			await ToolsSql.exec_update(sql, kwargs["nebula_db"])

	@staticmethod
	def select_credit(environment: str, source_user_id: str) -> dict:
		"""获取授信信息"""
		sql = f"""select * from credit where source_user_id='{source_user_id}';"""
		rep = ToolsSql.exec_select(environment, sql)
		if rep:
			credit_detail = json.loads(json.dumps(rep[0], cls=Encoder))
			return credit_detail
		else:
			logger_.info("未查询到授信信息")

	@staticmethod
	def select_project(environment: str, source_user_id: str) -> list:
		"""
		查询来源用户ID对应的进件
		"""
		sql = f"""select * from project_detail where source_user_id='{source_user_id}';"""
		project_detail = ToolsSql.exec_select(environment, sql)
		return project_detail

	@staticmethod
	def select_project_and_asset(environment: str, asset_id: str) -> dict:
		"""根据资产ID查询进件与资产信息"""
		sql = f"""
			select * from asset where id='{asset_id}';
		"""
		try:
			asset = ToolsSql.exec_select(environment, sql)[0]
		except IndexError:
			raise ValueError("不存在的资产ID")
		asset = _json_encode(ToolsSql.exec_select(environment, sql)[0])
		sql2 = f"""
			select * from project_detail where id='{asset["project_id"]}';
		"""
		project = _json_encode(ToolsSql.exec_select(environment, sql2)[0])
		asset["project"] = project
		return asset


class Statistics(ToolsSql):

	@staticmethod
	async def runcase_detail(sql: str, pool: Pool):
		await ToolsSql.exec_update(sql, pool)


def _json_encode(_json: dict) -> dict:
	return json.loads(json.dumps(_json, cls=Encoder))
